﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace GeneralClasses
{
    /// <summary>
    /// holds the location of a department
    /// </summary>
    public class Location
    {

        #region variables
        int lid; // id in the database
        String name; // the name of the location
        DatabaseConnection dbc;

        #endregion

        #region properties

        /*public String ConnectionString
        {
            get { return dbc.ConnectionString; }
            set { dbc.ConnectionString = value; }
        }*/

        public int LID
        {
            get { return lid; }
            set { lid = value; }
        }

        public String Name
        {
            get { return name; }
            set { name = value; }
        }

        #endregion

        #region constructor

        public Location()
        {
            lid = -1;
            name = "location X";
            dbc = new DatabaseConnection();
        }

        #endregion

        #region private

        private int removeLocationRelations()
        {
            try
            {
                if (lid != -1)
                {
                    dbc.executeNonQuery("DELETE FROM departmenthaslocationrelationship WHERE lid='" + lid + "'");
                    return 0;
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        private bool checkLocationUsed()
        {
            try
            {
                DataTable table = dbc.query("SELECT * FROM departmenthaslocationrelationship WHERE lid='" + lid + "'");
                if ((table != null) && table.Rows.Count > 0) { return true; }

                return false;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        /// <summary>
        /// returns whether or not this location exists in the database
        /// </summary>
        /// <returns></returns>
        private bool checkLocationExists()
        {
            try
            {
                DataTable table = dbc.query("SELECT lid FROM Location WHERE name='" + name + "'");
                return ((table != null) && table.Rows.Count > 0) ? (true) : (false);
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        private int findLocationID()
        {
            try
            {
                DataTable table = dbc.query("SELECT lid FROM Location WHERE name = '" + name + "'");
                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        return (int)row["lid"];
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        #endregion

        #region public

        /// <summary>
        /// saves the location data to the database
        /// </summary>
        public int saveData()
        {
            try
            { //if ID ==-1
                if (this.lid == -1)
                {
                    if (!checkLocationExists())
                    {
                        //add
                        dbc.executeNonQuery("INSERT INTO Location(name) values ('" + name + "')");
                    }
                    return (lid = findLocationID());
                }
                else
                {
                    //save
                    dbc.executeNonQuery("UPDATE Location SET name='" + this.name + "' WHERE lid='" + this.lid + "'");
                    return lid;
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }

        }

        /// <summary>
        /// loads all the data necessary for this class from it's own table
        /// like the others, make sure  you set the ID and the ConnectionString before loading
        /// </summary>
        public void loadData()
        {
            try
            {
                DataTable table = dbc.query("SELECT * FROM Location L WHERE L.lid = '" + lid + "'");

                if ((table != null) && table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        this.name = (string)row["name"];
                    }
                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }


        public int removeData()
        {
            try
            {
                if (lid != -1)
                {
                    this.removeLocationRelations();
                    if (!checkLocationUsed())
                    {
                        dbc.executeNonQuery("DELETE FROM Location WHERE lid='" + lid + "'");
                        return 0;
                    }
                }
                return -1;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                throw e;
            }
        }

        #endregion
    }
}
